package com.gitee.generator.universal.datasource.service.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import javax.sql.DataSource;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.db.meta.Column;
import cn.hutool.db.meta.MetaUtil;
import cn.hutool.db.meta.TableType;
import com.gitee.generator.universal.common.Constants;
import com.gitee.generator.universal.common.DataTypeEnum;
import com.gitee.generator.universal.domain.Project;
import com.gitee.generator.universal.domain.Table;
import com.gitee.generator.universal.domain.TableColumn;
import com.gitee.generator.universal.datasource.service.TableService;
import lombok.extern.slf4j.Slf4j;
/**
 * [表]Service
 * @since 2020-08-19 11:24
 * @author wsx
 */
@Slf4j
public class TableServiceImpl implements TableService {

    @Override
    public List<Table> listTablesWithColumns(Project cfg) {
        List<String> tableNameSet = new ArrayList<>();
        if(CollUtil.isNotEmpty(cfg.getTables())){
            cfg.getTables().forEach((key, value) -> {
                if(Boolean.TRUE.equals(MapUtil.getBool(value, Constants.GENERATE,true))){
                    tableNameSet.add(key);
                }
            });
            if(CollUtil.isEmpty(tableNameSet)){
                tableNameSet.add("");
            }
        }
        return listTablesWithColumns(cfg.getDbConfig().getDatasourceUrl(),cfg.getDbConfig().getUsername(),cfg.getDbConfig().getPassword(),cfg.getSchema(),cfg.getDomainIgnoreColumn(),tableNameSet);
    }
    /**
     * 查询所有[表],包括字段
     */
    @Override
    public List<Table> listTablesWithColumns(String url,String userName,String password,String schema,List<String> domainIgnoreColumn,List<String> generateTables) {
        DataSource dataSource =new SimpleDataSource(url,userName,password);
        List<Table> tables = new ArrayList<>();
        if(null == domainIgnoreColumn) {
            domainIgnoreColumn = new ArrayList<>();
        }
        try {
            Connection conn = dataSource.getConnection();
            DatabaseMetaData dbmd = conn.getMetaData();
            ResultSet tableSet = dbmd.getTables(null, schema, "%", new String[]{"TABLE", "VIEW"});

            while (tableSet.next()) {
                Table sqlTable = new Table();
                sqlTable.setTableName(tableSet.getString("TABLE_NAME"));
                if(!generateTables.contains(sqlTable.getTableName())){
                    continue;
                }
                tables.add(sqlTable);

                sqlTable.setRemarks(tableSet.getString("REMARKS"));
                sqlTable.setTableType(tableSet.getString("TABLE_TYPE"));
                sqlTable.setPkNames(new HashSet<>());
                sqlTable.setColumns(new ArrayList<>());
                sqlTable.setPkColumns(new ArrayList<>());
                sqlTable.setNeedImportClass(new HashSet<>());

                ResultSet primaryKeysResultSet = dbmd.getPrimaryKeys(null, schema, sqlTable.getTableName());
                while(primaryKeysResultSet.next()) {
                    sqlTable.getPkNames().add(primaryKeysResultSet.getString("COLUMN_NAME"));
                }

                ResultSet columnSet = dbmd.getColumns(null, schema, sqlTable.getTableName(), "%");
                for (int i = 1; columnSet.next(); i++) {
                    TableColumn tableColumn = new TableColumn();
                    tableColumn.setTableName(sqlTable.getTableName());
                    tableColumn.setColumnName(columnSet.getString("COLUMN_NAME"));

                    tableColumn.setColumnType(columnSet.getInt("DATA_TYPE"));
//                    log.info("{}-{}-{}",tableColumn.getTableName(),tableColumn.getColumnName(),tableColumn.getColumnType());
                    tableColumn.setColumnTypeName(DataTypeEnum.tranceDbType(tableColumn.getColumnType(),columnSet.getString("TYPE_NAME")));

                    tableColumn.setRemarks(columnSet.getString("REMARKS"));
                    tableColumn.setSize(columnSet.getInt("COLUMN_SIZE"));
                    tableColumn.setIsNullable(columnSet.getInt("NULLABLE") == 1);
                    tableColumn.setDefaultValue(columnSet.getString("COLUMN_DEF"));
                    tableColumn.setDecimalDigits(columnSet.getInt("DECIMAL_DIGITS"));
                    tableColumn.setSortOrder(i);
                    try {
                        tableColumn.setAutoIncrement("YES".equals(columnSet.getString("IS_AUTOINCREMENT")));
                    } catch (SQLException e) {
                        tableColumn.setAutoIncrement(false);
                        log.warn("获取IS_AUTOINCREMENT出现异常:", e);
                    }
                    tableColumn.setPk(sqlTable.getPkNames().contains(tableColumn.getColumnName()));

                    sqlTable.getColumns().add(tableColumn);
                    if(tableColumn.isPk()){
                        sqlTable.getPkColumns().add(tableColumn);
                    }
                    tableColumn.setShowInDomain(domainIgnoreColumn.stream().noneMatch(str->str.equalsIgnoreCase(tableColumn.getColumnName())));
                    if(tableColumn.getNeedImport() && tableColumn.isShowInDomain()){
                        sqlTable.getNeedImportClass().add(DataTypeEnum.getWholeFieldType(tableColumn.getColumnTypeName()));
                    }
                }
            }
        } catch (SQLException e) {
            log.error("查询失败",e);
            throw new RuntimeException(e);
        }

        return tables;
    }

//    @Override
    public List<Table> listTablesWithColumnsHutool(String url,String userName,String password,String schema,List<String> domainIgnoreColumn,List<String> generateTables) {
        DataSource dataSource =new SimpleDataSource(url,userName,password);

        List<Table> tables = new ArrayList<>();
        List<String> tableNames = MetaUtil.getTables(dataSource, schema, TableType.TABLE);
        if(null!=generateTables && !generateTables.isEmpty()){
            tableNames.retainAll(generateTables);
        }
        if(null == domainIgnoreColumn) {
            domainIgnoreColumn = new ArrayList<>();
        }
        for (String tableName : tableNames) {
            Table sqlTable = new Table();
            cn.hutool.db.meta.Table table = MetaUtil.getTableMeta(dataSource, tableName);
            sqlTable.setTableName(tableName);
            sqlTable.setRemarks(table.getComment());
            sqlTable.setPkNames(table.getPkNames());
            sqlTable.setColumns(new ArrayList<>());
            sqlTable.setPkColumns(new ArrayList<>());
            sqlTable.setNeedImportClass(new HashSet<>());
            Collection<Column> columns = table.getColumns();
            if (CollUtil.isNotEmpty(columns)) {
                int i = 0;
                for (Column column : columns) {
                    i++;
                    TableColumn tableColumn = new TableColumn();
                    tableColumn.setTableName(tableName);
                    tableColumn.setColumnName(column.getName());
                    tableColumn.setColumnType(column.getType());
                    tableColumn.setColumnTypeName(DataTypeEnum.tranceDbType(column.getType(),column.getTypeName()));
                    tableColumn.setRemarks(column.getComment());
                    tableColumn.setSize((int)column.getSize());
                    tableColumn.setIsNullable(column.isNullable());
                    tableColumn.setSortOrder(i);
                    tableColumn.setAutoIncrement(column.isAutoIncrement());
                    tableColumn.setPk(column.isPk());
//                    log.debug("字段信息  SqlTableColumn:{}", tableColumn);

                    sqlTable.getColumns().add(tableColumn);
                    if(tableColumn.isPk()){
                        sqlTable.getPkColumns().add(tableColumn);
                    }
                    tableColumn.setShowInDomain(domainIgnoreColumn.stream().noneMatch(str->str.equalsIgnoreCase(column.getName())));
                    if(tableColumn.getNeedImport() && tableColumn.isShowInDomain()){
                        sqlTable.getNeedImportClass().add(DataTypeEnum.getWholeFieldType(tableColumn.getColumnTypeName()));
                    }
                }
            }
            tables.add(sqlTable);
        }

        return tables;
    }

    public static void printResultSet(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        String header = "";
        for (int i = 1; i <= columnCount; i++) {
            header += metaData.getColumnName(i) + "\t";
        }
        System.out.println(header);

        while (rs.next()) {
            String row = "";
            for (int i = 1; i <= columnCount; i++) {
                row += rs.getString(i) + "\t";
            }
            System.out.println(row);
        }
    }

}